import openpyxl
import requests
headers = {"content-type": "application/json;charset=UTF-8", "path":"/Capture/Interactive/Find/v1.00/json3ex.ws?Key=BJ92-ZJ43-NW29-EC78&Text=Text%3DAB10%206TQ&Container=&Origin=GBR&Countries=&Datasets=&Limit=10&Filter=&Language=en&$block=true&$cache=true&SOURCE=PCA-SCRIPT&SESSION=dc9e2a98-938f-f21e-415d-be44965830fb",
          "scheme":"https", "origin":"https://www.royalmail.com","referer":"https://www.royalmail.com/","sec-ch-ua":'" Not;A Brand";v="99", "Google Chrome";v="91", "Chromium";v="91"'}

def  get_data(file_name):
    list_data = []
    with open(f"D:\\daochu\\UnitedKingdomPostcodes 2\\UnitedKingdomPostcodes\\{file_name}", "r", encoding="utf-8") as f:
        list_data.append(f.readlines())
        f.close()
    return list_data


def get_api_data(list_data):
    data = []
    for i in list_data[0]:
        url = f"https://services.postcodeanywhere.co.uk/Capture/Interactive/Find/v1.00/json3ex.ws?Key=BJ92-ZJ43-NW29-EC78&Text=={i}&Container=&Origin=&Countries=&Datasets=&Limit=10&Filter=&Language=en&$block=true&$cache=true&SOURCE=PCA-SCRIPT&SESSION=dc9e2a98-938f-f21e-415d-be44965830fb"
        r = requests.get(url, headers=headers)
        result = r.json().get("Items")
        for j in result:
            data_id = j.get("Id")
            if data_id:
                try:
                    url2 = f"https://services.postcodeanywhere.co.uk/Capture/Interactive/Find/v1.00/json3ex.ws?Key=BJ92-ZJ43-NW29-EC78&Text={i}&Container={data_id}&Origin=&Countries=&Datasets=&Limit=10&Filter=&Language=en&$block=true&$cache=true&SOURCE=PCA-SCRIPT&SESSION=eb96d565-f8aa-8bba-b94c-19404c61a7b7"
                    r = requests.get(url2, headers=headers)
                    result2 = r.json().get("Items")
                    for d in result2:
                        if d.get("Text"):
                            h = ['【' + i.split("\n")[0] + "】" + d.get("Text") + "| " + d.get("Description")]
                        else:
                            h = ['【' + i.split("\n")[0] + "】" + "null"]
                        data.append(h)
                except:
                    pass
    return data

def writeToExcel(file_path, data):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = '数据'
    if data:
        for r in range(len(data)):
            for c in range(len(data[0])):
                ws.cell(r + 1, c + 1).value = data[r][c]
        wb.save(file_path)  # 注意，写入后一定要保存
        print("成功写入文件: " + file_path + " !")


def write_data(file_list):
    index = 165
    for file in file_list:
        file_data = get_data(file)
        content_data = get_api_data(file_data)
        writeToExcel(f"D:\\daochu\\data\\data{index}.xls", content_data)
        index+=1
        print(file)

import os

data = os.listdir("D:\\daochu\\UnitedKingdomPostcodes 2\\UnitedKingdomPostcodes")
write_data(data)